﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;

namespace WCSY.DAL
{
    public class costsQueryService
    {
        /// <summary>
        /// 报销查询
        /// </summary>
        /// <param name="billtype">单据类型</param>
        /// <param name="dept">申请部门</param>
        /// <param name="applypsn">申请人</param>
        /// <param name="businessDateB">业务开始日期</param>
        /// <param name="businessDateE">业务结束日期</param>
        /// <param name="fillpsn">制单人</param>
        /// <param name="filldateB">制单开始日期</param>
        /// <param name="filldateE">制单结束日期</param>
        /// <param name="sysno"></param>
        /// <returns></returns>
        public DataTable getAllCostQueryByBillType(string billtype, string dept, string applypsn, string businessDateB, string businessDateE, string fillpsn, string filldateB, string filldateE, string sysno)
        {
            string sql = "";
            if (billtype.Equals("0"))
            {
                sql = "";//待填写
                return DBHelper.GetDataTable(sql);
            }
            else
            {
                string billdet = billtype.Substring(0, billtype.Length - 3)+"Det";
                sql = @"
            select mst.*,deptinfo.*,fg_empinfo.empname,det.locsum from " + billtype + " as mst " +
                @"left join fg_deptinfo as deptinfo on mst.deptno=deptinfo.id
            left join fg_empinfo on mst.applypsnno = fg_empinfo.id
            left join (select sysno,sum(isnull(applymoney,0)) as locsum from "+billdet+" group by sysno) as det on mst.sysno = det.sysno";
                sql += " where 1=1 ";
                if (!sysno.Equals(""))
                {
                    sql += " and mst.sysno like '%" + sysno + "%'";
                }
                if (!dept.Equals("0"))
                {
                    sql += " and mst.deptno = '" + dept + "'";
                }
                if (!applypsn.Equals(""))
                {
                    sql += " and fg_empinfo.empname like '%" + applypsn + "%'";
                }
                if (!fillpsn.Equals(""))
                {
                    sql += " and mst.fillpsn like '%" + fillpsn + "%'";
                }
                if (!filldateB.Equals(""))
                {
                    sql += " and mst.filldate>='" + filldateB + "'";
                }
                if (!filldateE.Equals(""))
                {
                    sql += " and mst.filldate<='" + filldateE + "' ";
                }
                if (!businessDateB.Equals(""))
                {
                    sql += " and mst.businessDateB>='" + businessDateB + "'";
                }
                if (!businessDateE.Equals(""))
                {
                    sql += " and mst.businessDateE<='" + businessDateE + "' ";
                }
                return DBHelper.GetDataTable(sql);
            }
        
        }
    }
}
